use strict;
use LWP::Simple;
use DBI;
use Config::IniFiles;
use XML::LibXML;

# This program read config_cdip.xml, get the spectral wave data file, parse it, then insert the data to the database. It will automatically update platform, sensor,sensor_type,obs_type,m_type,m_scalar_type,uom_type tables if no related record found. But it can't do the unit conversion yet.

my($platform_id,$platform_row_id,$latitude,$longitude,$platform_handle,$organization_row_id,@mdate,@frequency,@energy,@meanwave,@principalwave,$obs_type,$uom,@mvalue);  
my $timevalue="current_timestamp";

#config
my $cfg=Config::IniFiles->new( -file => '/var/myscheduler/dbConfig.ini');
my $db_name="rcoos";
my $tname=$cfg->val('rcoos','tname');
my $db_user=$cfg->val('rcoos','username');
my $db_passwd=$cfg->val('rcoos','password');

#for postgres database
#establish database connection
my $dbh = DBI->connect ( "dbi:Pg:dbname=$db_name", $db_user, $db_passwd);
if ( !defined $dbh ) {
       die "Cannot connect to database!\n";
       exit 0;
} 

my $target_dir = '/tmp/response';
my $config_filepath = '/var/myscheduler/config_ndbc.xml';
my $config = XML::LibXML->new->parse_file($config_filepath);


foreach my $provider ($config->findnodes('//provider')) {
	my $data_url = $provider->findvalue('dataURL'); 
        my $provider_url=$provider->findvalue('url');
	my $organization=$provider->findvalue('name');
 	
	my $ndbcId = $provider->findvalue('ndbcId');
	$platform_handle = $provider->findvalue('platformHandle');
	my $platform_Id = $provider->findvalue('platformId');
        my $thecount=$provider->findvalue('count');
        my ($xml_url,$startcount);

        @mdate=();
        @energy=();
        @frequency=();
        @meanwave=();
        @principalwave=();

	my $sql = qq{ SELECT row_id from organization where short_name like '$organization' };
	#print $sql."\n";
	my $sth = $dbh->prepare( $sql );
	$sth->execute();
	($organization_row_id) = $sth->fetchrow_array;
        
	if ($organization_row_id) {} #print "$organization_name_lkp\n"; 
	else {
		print "organization not found - inserting\n";
		$sql = qq{ SELECT max(row_id) from organization  };
		$sth = $dbh->prepare( $sql );
		$sth->execute();
		($organization_row_id) = $sth->fetchrow_array;	        
                $organization_row_id=$organization_row_id+1;
		$sql = qq{ INSERT into organization(row_id,row_entry_date,short_name,url) values ($organization_row_id,$timevalue,'$organization','$provider_url'); };
		#print $sql."\n";
		$sth = $dbh->prepare( $sql );
		$sth->execute();
                $sth->finish();
         
	}
	##platform####################
	$sql = qq{ SELECT row_id,fixed_latitude,fixed_longitude,short_name,platform_handle from platform where organization_id = $organization_row_id and short_name = '$platform_Id' };
	#print $sql."\n";
	$sth = $dbh->prepare( $sql );
	$sth->execute();
	($platform_row_id,$latitude,$longitude) = $sth->fetchrow_array;

	if ($platform_row_id) {
            ;
        } #print "$platform_name_lkp\n"; }
	else {
		print "platform not found - inserting\n";

		#requery for new row_id
		$sql = qq{ SELECT max(row_id) from platform  };
		#print $sql."\n";
		$sth = $dbh->prepare( $sql );
		$sth->execute();
		($platform_row_id) = $sth->fetchrow_array;
                $platform_row_id++;
                my $undefined_platform_type_id=6;
                #$platform_desc=~ s/,//g;
                my $platform_url=$provider_url."/station_page.php?station=$platform_Id";
		$sql = "INSERT into platform(row_id,row_entry_date,organization_id,type_id,short_name,platform_handle) values ($platform_row_id,$timevalue,$organization_row_id,$undefined_platform_type_id,'$platform_Id','$platform_handle')";
		print $sql."\n";
		$sth = $dbh->prepare( $sql );
		$sth->execute();
                $sth->finish();
        }
   

        #frist get spectral wave nad center frequency        
        $xml_url = $data_url."$ndbcId".".data_spec";
        #generate a random file handle concatenating a random number
        my $xml_filepath = "$target_dir/".$platform_Id."latest.txt";
        print $xml_url."\n";
        if(is_success(getstore($xml_url, $xml_filepath))){
        
        open(FILE,"<$xml_filepath");
        my @data=<FILE>;
        close(FILE);
        #parse the file
        for(my $i=0;$i<@data;$i++){
             
             if(!($data[$i]=~ /#YY/)){
                 
                 my @temparray=split(/\n/,$data[$i]);
                 $data[$i]=$temparray[0];
                 my @temparray=split(/ /,$data[$i]);
                 my $datetime=$temparray[0]."-".$temparray[1]."-".$temparray[2]." ".$temparray[3].":".$temparray[4];
                 my $freq;
                 my $spectral;
                 for(my $a=6;$a<@temparray;$a++){
                    if($a%2==1){
                      $freq=$freq.substr($temparray[$a],1,-1).";";
                    }
                    else {
                       $spectral=$spectral.$temparray[$a].";";
                    }
                 }
                 
                 $spectral=substr($spectral,0,-1);
                 $freq=substr($freq,0,-1);
                 push(@mdate,$datetime);
                 push(@frequency,$freq);
                 push(@energy,$spectral);
                 #print $datetime."\n";
                 #print $spectral."\n";
                 if($i>($thecount+1)){last;}
             }
        }#end of parsing wavespectral file
        #now insert the centerfrequency and spectral to database
 
        $uom="";
        $obs_type="spectral_energy";
        @mvalue=@energy;
        my $date1=$mdate[0];
        insert();
        $obs_type="center_frequencies";
        @mvalue=@frequency;
        insert();

        my @filearray=("swdir","swdir2","swr1","swr2");
        my @ttype=("mean_wave_direction","principal_wave_direction","polar_coordinate_r_1","polar_coordinate_r_2");

        for(my $b=0;$b<@filearray;$b++){
        @mdate=();
        @mvalue=();
        #get mean wave direction        
        $xml_url = $data_url."$ndbcId".".".$filearray[$b];
        #generate a random file handle concatenating a random number
        my $xml_filepath = "$target_dir/".$platform_Id."latest.txt";
        print $xml_url."\n";
        if(is_success(getstore($xml_url, $xml_filepath))){
        #die "Couldn't get $xml_url" unless defined $content; 
        open(FILE,"<$xml_filepath");
        my @data=<FILE>;
        close(FILE);
        #parse the file
        my $i=0;
        for($i=0;$i<@data;$i++){
             
             if(!($data[$i]=~ /#YY/)){
                 
                 my @temparray=split(/\n/,$data[$i]);
                 $data[$i]=$temparray[0];
                 my @temparray=split(/ /,$data[$i]);
                 my $datetime=$temparray[0]."-".$temparray[1]."-".$temparray[2]." ".$temparray[3].":".$temparray[4];
                 
                 if($datetime gt $date1){
                      next;
                 }
                 


                 my $freq;
                 my $mvalue_s;
                 for(my $a=5;$a<@temparray;$a++){
                    if($a%2==0){
                      $freq=$freq.substr($temparray[$a],1,-1).";";
                    }
                    else {
                       $mvalue_s=$mvalue_s.$temparray[$a].";";
                    }
                 }
                 
                 $mvalue_s=substr($mvalue_s,0,-1);
                 push(@mdate,$datetime);
                 push(@mvalue,$mvalue_s);
                 
                 if($i>($thecount+1)){last;}
             }#end of data line
        }#end of parsing file file
       if($i>0){
         $obs_type=$ttype[$b];
         insert();
        }
        } #end of filearray
       }
     }
}


sub insert{

   my $m_type_id=getMtype($obs_type,$uom);
   my $sensor_id=getSensor($m_type_id,$obs_type);
 
   my $missing_value="null";        

   for(my $x=0;$x<@mdate;$x++){
      my $sql="select row_id from $tname where m_date='$mdate[$x]' and sensor_id=$sensor_id and m_type_id=$m_type_id";
      my $sth = $dbh->prepare($sql);
      $sth->execute();
      my ($row_id) = $sth->fetchrow_array;
      $sth->finish();

      if($row_id>0){;}
      else {
                   
              $sql="INSERT into $tname (row_entry_date,platform_handle,sensor_id,m_type_id,m_date,m_lon,m_lat,m_z,m_value_list) values ($timevalue,'$platform_handle',$sensor_id,$m_type_id,'$mdate[$x]',$longitude,$latitude,$missing_value,'$mvalue[$x]')";
        #      print $sql."\n";
              $sth = $dbh->prepare($sql);         
              $sth->execute();
              $sth->finish();
              
      }
  }#end of mdate array 
}


sub getMtype{
     my ($obs_type,$uom_type)=@_;
     my $sql="select m_type.row_id,uom_type.display from m_type 
              left join m_scalar_type on m_scalar_type.row_id=m_type.m_scalar_type_id
              left join obs_type on obs_type.row_id=m_scalar_type.obs_type_id
              left join uom_type on uom_type.row_id=m_scalar_type.uom_type_id
              where obs_type.standard_name='$obs_type'";
               # print "\n"."$sql\n";
     my $sth = $dbh->prepare($sql);
        $sth->execute();
     my ($m_type_id,$uom_name) = $sth->fetchrow_array;
     $sth->finish();

     if(!$m_type_id){

         #updtae obs_type table
         $sql="select row_id from obs_type where standard_name='$obs_type'";
         $sth = $dbh->prepare($sql);
         $sth->execute();
         my ($obstype_id) = $sth->fetchrow_array;
         if(!$obstype_id){
           $sql="select max(row_id) from obs_type";
           $sth = $dbh->prepare($sql);
           $sth->execute();
           my ($obstype_id) = $sth->fetchrow_array;
           $obstype_id++;
           $sql="insert into obs_type(row_id,standard_name) values($obstype_id,'$obs_type')";
           $sth = $dbh->prepare($sql);
           print "$sql\n";
           $sth->execute();
           $sth->finish();
          }
           #updtae uom_type table
           $sql="select row_id from uom_type where display='$uom_type'";
           $sth = $dbh->prepare($sql);
           $sth->execute();
           my ($uom_id) = $sth->fetchrow_array;
           if(!$uom_id){
             $sql="select max(row_id) from uom_type";
             $sth = $dbh->prepare($sql);
             $sth->execute();
             my ($uom_id) = $sth->fetchrow_array;
             $uom_id++;
             $sql="insert into uom_type(row_id,standard_name,display) values($uom_id,'$uom_type','$uom_type')";
             $sth = $dbh->prepare($sql);
             print "$sql\n";
             $sth->execute();
             $sth->finish();
            }

        #updtae m_scalar_type table
         $sql="select row_id from m_scalar_type where obs_type_id=$obstype_id and uom_type_id=$uom_id";
         $sth = $dbh->prepare($sql);
         $sth->execute();
         my ($mscalar_id) = $sth->fetchrow_array;
         if(!$mscalar_id){
           $sql="select max(row_id) from m_scalar_type";
           $sth = $dbh->prepare($sql);
           $sth->execute();
           my ($mscalar_id) = $sth->fetchrow_array;
           $mscalar_id++;
           $sql="insert into m_scalar_type(row_id,obs_type_id,uom_type_id) values($mscalar_id,$obstype_id,$uom_id)";
           print "$sql\n";
           $sth = $dbh->prepare($sql);
           $sth->execute();
           $sth->finish();
          }

                  #update mtype table   
          $sql="select max(row_id) from m_type";
          $sth = $dbh->prepare($sql);
          $sth->execute();
          my ($m_type_id) = $sth->fetchrow_array;
          $m_type_id++;
          $sql="insert into m_type(row_id,num_types,description,m_scalar_type_id) values($m_type_id,1,'$obs_type',$mscalar_id)";
          #print "$sql\n";
          $sth = $dbh->prepare($sql);
          $sth->execute();
          $sth->finish();

     } #end of m_type_id

     return $m_type_id;
}

sub getSensor{
  
    my ($m_type_id,$short_name)=@_;
    my $sql="select row_id from sensor where platform_id=$platform_row_id and m_type_id=$m_type_id";
    
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    # print $sql."\n";
    my ($sensor_id) = $sth->fetchrow_array;
    $sth->finish();
    if(!$sensor_id){
         #insert new record to sensor table
         $sql="select row_id from sensor_type where type_name='$short_name'";
         $sth = $dbh->prepare($sql);
         $sth->execute();
         my ($type_id) = $sth->fetchrow_array;
         $sth->finish();
         if(!$type_id){
           $sql="select max(row_id) from sensor_type";
           $sth = $dbh->prepare($sql);
           $sth->execute();
           my ($type_id) = $sth->fetchrow_array;
           $type_id++;
           $sql="insert into sensor_type(row_id,type_name,description) values($type_id,'$short_name','$short_name')";
           $sth = $dbh->prepare($sql);
           #print "$sql\n";
           $sth->execute();
           $sth->finish();
         }
         $sql="select max(row_id) from sensor";
         $sth = $dbh->prepare($sql);
         $sth->execute();
         ($sensor_id) = $sth->fetchrow_array;
         $sensor_id++;
         $sql="insert into sensor(row_id,row_entry_date,platform_id,type_id,m_type_id,short_name) values ($sensor_id,$timevalue,$platform_row_id,$type_id,$m_type_id,'$short_name')";
          $sth = $dbh->prepare($sql);
          #print "inserting to sensor table $sql\n";
          $sth->execute();
          $sth->finish();
    }
    return $sensor_id;
}

